package tt.dz.biz.dao.thirdaccount;

import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.object.BatchSqlUpdate;
import org.springframework.stereotype.Repository;
import tt.dz.biz.dao.BaseDAO;
import tt.dz.common.BusinessException;
import tt.dz.common.DzErrorCode;
import tt.dz.common.DzErrorCode.ThirdAccountErrorCode;
import tt.dz.vo.ThirdAccountVo;
import tt.dz.vo.UserBaseVO;
import tt.dz.vo.object.ThirdQueryUser;

@Repository("thirdAccountDao")
public class ThirdAccountDaoImpl
  extends BaseDAO
{
  @Autowired
  @Qualifier("dataSource")
  private DataSource ds;
  private static final String LIST_THIRD_ACCOUNT_CLOUMNS_SQL = " select ta.user_id , ta.third_uuuid , ta.bind_type , ta.is_validate , ta.bind_time from ms_third_party_binding_account ta  ";
  private static final String COUNT_THIRD_ACCOUNT_CLOUMNS_SQL = " select count(1) from ms_third_party_binding_account ta  ";
  private static final String LIST_THIRD_ACCOUNT_SQL = " select ta.user_id , ta.third_uuuid , ta.bind_type , ta.is_validate , ta.bind_time from ms_third_party_binding_account ta   where 1=1 ";
  private static final String QUERY_THIRD_ACCOUNT_SQL = " select ta.user_id , ta.third_uuuid , ta.bind_type , ta.is_validate , ta.bind_time from ms_third_party_binding_account ta   where 1=1  and ta.user_id =:userId and  ta.third_uuuid =:thirdUuuid and  ta.bind_type=:bindType and   ta.is_validate=:isValidate and ta.bind_time=:bindTime  ";
  private static final String QUERY_THIRD_ACCOUNT_BY_USER_ID_BIND_INFO_SQL = " select ta.user_id , ta.third_uuuid , ta.bind_type , ta.is_validate , ta.bind_time from ms_third_party_binding_account ta   where 1=1  and ta.user_id =:userId   and  ta.bind_type=:bindType and  ta.third_uuuid =:thirdUuuid     ";
  private static final String QUERY_THIRD_ACCOUNT_BY_USER_ID_BIND_TYPE_SQL = " select ta.user_id , ta.third_uuuid , ta.bind_type , ta.is_validate , ta.bind_time from ms_third_party_binding_account ta   where 1=1  and ta.user_id =:userId   and  ta.bind_type=:bindType     ";
  private static final String QUERY_THIRD_ACCOUNT_BY_USER_SQL = " select ta.user_id , ta.third_uuuid , ta.bind_type , ta.is_validate , ta.bind_time from ms_third_party_binding_account ta   where 1=1  and  ta.user_id = :userId   ";
  private static final String INSERT_THIRD_ACCOUNT_SQL = " insert into ms_third_party_binding_account (  user_id , third_uuuid , bind_type , is_validate , bind_time )  values (   :userId , :thirdUuuid , :bindType , :isValidate , :bindTime )  ";
  private static final String UPDATE_THIRD_ACCOUNT_ALIDATE_SQL = " update  ms_third_party_binding_account set  is_validate= :isValidate , bind_time=:bindTime where user_id = :userId and third_uuuid =:thirdUuuid and bind_type =:bindType ";
  private static final String UPDATE_THIRD_ACCOUNT_THIRD_UUUID_SQL = " update  ms_third_party_binding_account set  third_uuuid=:newThirdUuuid , bind_time=:bindTime where user_id = :userId and third_uuuid =:thirdUuuid and bind_type=:bindType  ";
  private static final String DELETE_THIRD_ACCOUNT_SQL = " DELETE from ms_third_party_binding_account WHERE   user_id = :userId and  third_uuuid=:thirdUuuid and bind_type=:bindType  ";
  private static final String DELETE_THIRD_ACCOUNT_FOR_BATCH_SQL = " DELETE from ms_third_party_binding_account WHERE   user_id = ? and  third_uuuid=? and bind_type=?  ";
  private static final String DELETE_THIRD_ACCOUNT_BY_USER_SQL = " DELETE from ms_third_party_binding_account WHERE   user_id = :userId  ";
  public static final String LIST_USER_BASE = "select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.balance,t.allow_over_quota,t.login_times,t.is_online,t.password,t.salt,t.is_locked,t.locked_time,t.failure_times,t.is_valid,t.notify_url,t.enterprise_code  ";
  private static final String QUERY_USER_BASE_BY_THIRD_TYPE_THIRD_UUUID_SQL = "select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.balance,t.allow_over_quota,t.login_times,t.is_online,t.password,t.salt,t.is_locked,t.locked_time,t.failure_times,t.is_valid,t.notify_url,t.enterprise_code   from ms_user_base t inner join ms_third_party_binding_account a on  a.user_id = t.user_id  where  t.merchant_code=:merchantCode  and a.third_uuuid=:thirdUuuid ";
  private static final String QUERY_THIRD_ACCOUNT_BY_BIND_TYPE_AND_THIRD_ACCOUNT_ID_SQL = " select ta.user_id , ta.third_uuuid , ta.bind_type , ta.is_validate , ta.bind_time from ms_third_party_binding_account ta   inner join ms_user_base a on  a.user_id = ta.user_id  where  a.merchant_code=:merchantCode  and ta.third_uuuid=:thirdUuuid  and ta.bind_type=:bindType ";
  private static final String QUERY_THIRD_ACCOUNT_BY_BIND_TYPE_AND_THIRD_ACCOUNT_ID_SQL_COUNT = " select count(1) from ms_third_party_binding_account ta   inner join ms_user_base a on  a.user_id = ta.user_id  where  a.merchant_code=:merchantCode  and ta.third_uuuid=:thirdUuuid  and ta.bind_type=:bindType ";
  private static final String EXIST_USER_ID_THIRD_UUUID_REPEAT = " select count(1) from ms_third_party_binding_account where 1=1 and user_id =:userId and  third_uuuid=:thirdUuuid  ";
  
  public int deleteThirdAccountVo(ThirdAccountVo thirdAccountVo)
  {
    return save(" DELETE from ms_third_party_binding_account WHERE   user_id = :userId and  third_uuuid=:thirdUuuid and bind_type=:bindType  ", thirdAccountVo);
  }
  
  public int deleteUserThirdAccount(String userId)
  {
    ThirdAccountVo vo = new ThirdAccountVo();
    
    vo.setUserId(userId);
    
    return save(" DELETE from ms_third_party_binding_account WHERE   user_id = :userId  ", vo);
  }
  
  public List<ThirdAccountVo> queryAll()
  {
    return queryForList(" select ta.user_id , ta.third_uuuid , ta.bind_type , ta.is_validate , ta.bind_time from ms_third_party_binding_account ta   where 1=1  and ta.user_id =:userId and  ta.third_uuuid =:thirdUuuid and  ta.bind_type=:bindType and   ta.is_validate=:isValidate and ta.bind_time=:bindTime  ", null, ThirdAccountVo.class);
  }
  
  public List<ThirdAccountVo> queryByUserId(String userId)
  {
    ThirdAccountVo vo = new ThirdAccountVo();
    
    vo.setUserId(userId);
    
    return queryForList(" select ta.user_id , ta.third_uuuid , ta.bind_type , ta.is_validate , ta.bind_time from ms_third_party_binding_account ta   where 1=1  and  ta.user_id = :userId   ", vo, ThirdAccountVo.class);
  }
  
  public ThirdAccountVo queryByUserIdAndBindTypeAndThirdUuuid(String userId, String bindType, String thirdUuuid)
  {
    ThirdAccountVo vo = new ThirdAccountVo();
    
    vo.setUserId(userId);
    vo.setBindType(bindType);
    vo.setThirdUuuid(thirdUuuid);
    
    return (ThirdAccountVo)queryForObject(" select ta.user_id , ta.third_uuuid , ta.bind_type , ta.is_validate , ta.bind_time from ms_third_party_binding_account ta   where 1=1  and ta.user_id =:userId   and  ta.bind_type=:bindType and  ta.third_uuuid =:thirdUuuid     ", vo, ThirdAccountVo.class);
  }
  
  public int saveThirdAccountVo(ThirdAccountVo thirdAccountVo)
  {
    return save(" insert into ms_third_party_binding_account (  user_id , third_uuuid , bind_type , is_validate , bind_time )  values (   :userId , :thirdUuuid , :bindType , :isValidate , :bindTime )  ", thirdAccountVo);
  }
  
  public int updateBindThirdAccountVo(String userId, String bindType, String thirdUuuid, String newThirdUuuid)
  {
    ThirdAccountVo vo = new ThirdAccountVo();
    
    vo.setUserId(userId);
    vo.setBindType(bindType);
    vo.setThirdUuuid(thirdUuuid);
    vo.setNewThirdUuuid(newThirdUuuid);
    
    return save(" update  ms_third_party_binding_account set  third_uuuid=:newThirdUuuid , bind_time=:bindTime where user_id = :userId and third_uuuid =:thirdUuuid and bind_type=:bindType  ", vo);
  }
  
  public int updateThirdAccountVo(ThirdAccountVo thirdAccountVo)
  {
    return save(" update  ms_third_party_binding_account set  is_validate= :isValidate , bind_time=:bindTime where user_id = :userId and third_uuuid =:thirdUuuid and bind_type =:bindType ", thirdAccountVo);
  }
  
  public List<ThirdAccountVo> queryThirdAccoutByUserIdAndBindType(String userId, String bindType)
  {
    ThirdAccountVo vo = new ThirdAccountVo();
    vo.setUserId(userId);
    vo.setBindType(bindType);
    return queryForList(" select ta.user_id , ta.third_uuuid , ta.bind_type , ta.is_validate , ta.bind_time from ms_third_party_binding_account ta   where 1=1  and ta.user_id =:userId   and  ta.bind_type=:bindType     ", vo, ThirdAccountVo.class);
  }
  
  public void bathDeteleAccounts(ThirdAccountVo[] thirdAccountVos)
    throws DataAccessException
  {
    int count = 0;
    BatchSqlUpdate bsu = new BatchSqlUpdate(this.ds, " DELETE from ms_third_party_binding_account WHERE   user_id = ? and  third_uuuid=? and bind_type=?  ");
    bsu.setBatchSize(100);
    bsu.setTypes(new int[] { 12, 12, 12 });
    for (ThirdAccountVo vo : thirdAccountVos) {
      bsu.update(new Object[] { vo.getUserId(), vo.getThirdUuuid(), vo.getBindType() });
    }
    bsu.flush();
  }
  
  public UserBaseVO queryByBindTypeAndThirdUuuid(String thirdUuuid, String merchantCode)
    throws BusinessException
  {
    ThirdQueryUser vo = new ThirdQueryUser();
    vo.setThirdUuuid(thirdUuuid);
    vo.setMerchantCode(merchantCode);
    try
    {
      return (UserBaseVO)queryForObject("select  t.user_id,t.mobile_no,t.regist_time,t.email,t.name,t.gender,t.id_type,t.id_no,t.user_type,t.quality,t.merchant_code,t.quota,t.balance,t.allow_over_quota,t.login_times,t.is_online,t.password,t.salt,t.is_locked,t.locked_time,t.failure_times,t.is_valid,t.notify_url,t.enterprise_code   from ms_user_base t inner join ms_third_party_binding_account a on  a.user_id = t.user_id  where  t.merchant_code=:merchantCode  and a.third_uuuid=:thirdUuuid ", vo, UserBaseVO.class);
    }
    catch (EmptyResultDataAccessException e)
    {
      return null;
    }
    catch (IncorrectResultSizeDataAccessException e)
    {
      throw new BusinessException(DzErrorCode.ThirdAccountErrorCode.QUERY_MULTI_BIND.value(), DzErrorCode.ThirdAccountErrorCode.QUERY_MULTI_BIND.getDesc(), "一个第三方账户存在多个用户绑定关系");
    }
    catch (Exception e) {}
    return null;
  }
  
  public List<ThirdAccountVo> queryThirdAccuntByBindTypeAndThirdUuuidAndMerchantCode(String bindType, String thirdUuuid, String merchantCode)
  {
    ThirdQueryUser vo = new ThirdQueryUser();
    vo.setThirdUuuid(thirdUuuid);
    vo.setBindType(bindType);
    vo.setMerchantCode(merchantCode);
    return queryForList(" select ta.user_id , ta.third_uuuid , ta.bind_type , ta.is_validate , ta.bind_time from ms_third_party_binding_account ta   inner join ms_user_base a on  a.user_id = ta.user_id  where  a.merchant_code=:merchantCode  and ta.third_uuuid=:thirdUuuid  and ta.bind_type=:bindType ", vo, ThirdAccountVo.class);
  }
  
  public boolean isExistUUUIDUserIdRepeat(String userId, String thirdUuuid)
  {
    ThirdAccountVo vo = new ThirdAccountVo();
    vo.setUserId(userId);
    vo.setThirdUuuid(thirdUuuid);
    Number count = (Number)getJdbcTemplate().queryForObject(" select count(1) from ms_third_party_binding_account where 1=1 and user_id =:userId and  third_uuuid=:thirdUuuid  ", new BeanPropertySqlParameterSource(vo), Integer.class);
    return count.intValue() > 0;
  }
  
  public int queryThirdAccuntByBindTypeAndThirdUuuidAndMerchantCodeCount(String bindType, String thirdUuuid, String merchantCode)
  {
    ThirdQueryUser vo = new ThirdQueryUser();
    vo.setThirdUuuid(thirdUuuid);
    vo.setBindType(bindType);
    vo.setMerchantCode(merchantCode);
    BeanPropertySqlParameterSource sqlParameterSource = new BeanPropertySqlParameterSource(vo);
    Number number = (Number)getJdbcTemplate().queryForObject(" select count(1) from ms_third_party_binding_account ta   inner join ms_user_base a on  a.user_id = ta.user_id  where  a.merchant_code=:merchantCode  and ta.third_uuuid=:thirdUuuid  and ta.bind_type=:bindType ", sqlParameterSource, Integer.class);
    return number == null ? 0 : number.intValue();
  }
}
